package edu.hunan.cxb18.jdbc.dao.impl;

import edu.hunan.cxb18.jdbc.dao.UserDao;
import edu.hunan.cxb18.jdbc.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

/**
 * @author chenheng
 * @create 2020-03-24 8:20
 */
@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int insertUser(User user) {
        String sql = "insert into t_user(username,pwd) values(?,?)";
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql,  new String[] { "id" });
                psst.setString(1, user.getUsername());
                psst.setString(2, user.getPwd());
                return psst;
            }
        }, generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public String queryNameById(Integer id) {
        return jdbcTemplate.queryForObject("select username from t_user where id=?",String.class,id);
    }

    @Override
    public User queryUserById(Integer id) {
        String sql = "select id,username,pwd,dotime,isActive from t_user where id=?";
        Object[] args={id};
        return jdbcTemplate.queryForObject(sql,args,(rs,i)->{
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPwd(rs.getString("pwd"));
            Timestamp timestamp = rs.getTimestamp("dotime");
            user.setDotime(timestamp.toLocalDateTime());
            user.setActice(rs.getBoolean("isActive"));
            return user;
        });
    }

    @Override
    public List<User> queryUsersByName(String username) {
        String sql = "select id,username,pwd,dotime,isActive from t_user where username like concat('%',?,'%')";
        Object[] args={username};
        return jdbcTemplate.query(sql,args,(rs,i)->{
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPwd(rs.getString("pwd"));
            Timestamp timestamp = rs.getTimestamp("dotime");
            user.setDotime(timestamp.toLocalDateTime());
            user.setActice(rs.getBoolean("isActive"));
            return user;
        });
    }
}
